﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using bdnt_pets_system_Entity;
using System.Data;

namespace bdnt_pets_system_Bo.SystemManager
{
    public class MatePartManager
    {
        AccessHelper accessHelper = AccessHelper.GetInstance();

        public Int32 SaveObj(List<Tbl_MatePart> cuObjs)
        {
            StringBuilder sql = new StringBuilder();
            int i = 0;
            sql.Append(@"INSERT INTO Tbl_MatePart(VINNO,PARTNO,BARNO,CREATETIME,CREATEUSERID)");
            foreach (var item in cuObjs)
            {
                sql.AppendLine();
                if (i > 0)
                {
                    sql.Append(@"union all");
                    sql.AppendLine();
                }
                sql.AppendFormat(@"select '{0}','{1}','{2}',{3},{4} from dual", item.VINNO, item.PARTNO, item.BARNO,
               (item.CREATETIME.HasValue && item.CREATETIME.Value.Year != 1900 ?
               "to_date('" + item.CREATETIME.Value.ToString("yyyy-MM-dd") + "','yyyy-MM-dd')" : "NULL"),
                item.CREATEUSERID);
                i++;
            }

            return accessHelper.ExecuteNonQuery(sql.ToString());
        }

        public bool SaveOneObj(Tbl_MatePart cuObj)
        {
            string sql = string.Format(@"INSERT INTO Tbl_MatePart(VINNO,PARTNO,BARNO,CREATETIME,CREATEUSERID)
                  Values('{0}','{1}','{2}',{3},{4})", cuObj.VINNO, cuObj.PARTNO, cuObj.BARNO,
                (cuObj.CREATETIME.HasValue && cuObj.CREATETIME.Value.Year != 1900 ?
                "to_date('" + cuObj.CREATETIME.Value.ToString("yyyy-MM-dd") + "','yyyy-MM-dd')" : "NULL"),
                 cuObj.CREATEUSERID);
            return accessHelper.ExcultData(sql);
        }
        public bool DeleteObj(Int32 objId)
        {
            string sql = string.Format(@"delete from Tbl_MatePart where id={0}", objId);
            return accessHelper.ExcultData(sql);
        }

        public List<Tbl_MatePart> GetObjListByCondition(string vinNo, int pageSize, int currentPage, string stationIds)
        {
            StringBuilder sql = new StringBuilder(string.Format(@"select distinct mt.*
            ,pt.PartName,pt.en_PartName,(select empname from tbl_employee where id=mt.createuserid and rownum=1) empname
            from tbl_matepart mt 
            left join Tbl_Part pt on mt.PartNo=pt.PartNo 
            left join Tbl_PartSta_Relation tpr on tpr.partno=mt.partno  
            where tpr.id is not null and tpr.StationId in ({0})", stationIds));
            
            if (!string.IsNullOrEmpty(vinNo))
                sql = sql.AppendFormat(@" and mt.vinno like '%{0}%'", vinNo);

            int startNum = pageSize * (currentPage > 0 ? currentPage - 1 : 0);
            int endNum = pageSize * currentPage;            
            sql.Insert(0, "select row_number() over(order by re.Id desc) recordId,re.* from (");
            sql.AppendFormat(@") re ");
            sql.Insert(0, "select re.* from (");
          
            sql.AppendFormat(@") re where re.recordId>{0} and re.recordId<={1}", startNum, endNum);
            var dt = accessHelper.ExcuteDataTable(sql.ToString());

            List<Tbl_MatePart> returnlist = new List<Tbl_MatePart>();
            foreach (DataRow dr in dt.Rows)
            {
                returnlist.Add(new Tbl_MatePart
                {
                    Id = Convert.ToInt32(dr["Id"]),
                    BARNO = dr["BARNO"] == DBNull.Value ? "" : dr["BARNO"].ToString(),
                    CREATEUSERID = dr["CREATEUSERID"] == DBNull.Value ? 0 : Convert.ToInt32(dr["CREATEUSERID"]),
                    PARTNO = dr["PARTNO"] == DBNull.Value ? "" : dr["PARTNO"].ToString(),
                    VINNO = dr["VINNO"] == DBNull.Value ? "" : dr["VINNO"].ToString(),
                    CREATETIME = dr["CREATETIME"] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr["CREATETIME"].ToString()),
                    CreateUserName = dr["empname"] == DBNull.Value ? "" : dr["empname"].ToString(),
                    en_PartName = dr["en_PartName"] == DBNull.Value ? "" : dr["en_PartName"].ToString(),
                    PartName = dr["PartName"] == DBNull.Value ? "" : dr["PartName"].ToString()
                });
            }
            return returnlist;
        }

        public int GetObjListByConditionCount(string vinNo, string stationIds)
        {
            StringBuilder sql = new StringBuilder(string.Format(@"select count(distinct(mt.Id)) allCount
            from tbl_matepart mt 
            left join Tbl_Part pt on mt.PartNo=pt.PartNo 
            left join Tbl_PartSta_Relation tpr on tpr.partno=mt.partno   
            where tpr.id is not null and tpr.StationId in ({0})", stationIds));

            if (!string.IsNullOrEmpty(vinNo))
                sql = sql.AppendFormat(@" and mt.vinno like '%{0}%'", vinNo);

            var dt = accessHelper.ExecuteScalar(sql.ToString());
            if (dt != null)
                return Int32.Parse(dt.ToString());
            return 0;
        }

        public List<Tbl_MatePart> GetMatePartStationListByConditon(string stationIds, string vinno)
        {
            StringBuilder sql = new StringBuilder(string.Format(@"select distinct mp.Id,mp.barno,tpr.PartNo,pt.PartName,pt.en_PartName 
            from Tbl_PartSta_Relation tpr
            left join Tbl_Part pt on tpr.PartNo=pt.PartNo 
            left join tbl_matepart mp on mp.vinno='{0}' and mp.partno=pt.partno
            where pt.isimportant=1 ", vinno));

            if (!string.IsNullOrEmpty(stationIds))
                sql = sql.AppendFormat(@" and tpr.StationId in ({0})", stationIds);
            sql.Append(@" order by pt.en_PartName,pt.PartName");
            var dt = accessHelper.ExcuteDataTable(sql.ToString());

            List<Tbl_MatePart> returnlist = new List<Tbl_MatePart>();
            foreach (DataRow dr in dt.Rows)
            {
                returnlist.Add(new Tbl_MatePart()
                {
                    Id = dr["Id"] == DBNull.Value ? 0 : Int32.Parse(dr["Id"].ToString()),
                    en_PartName = dr["en_PartName"] == DBNull.Value ? "" : dr["en_PartName"].ToString(),
                    PartName = dr["PartName"] == DBNull.Value ? "" : dr["PartName"].ToString(),
                    PARTNO = dr["PartNo"] == DBNull.Value ? "" : dr["PartNo"].ToString(),
                    BARNO = dr["barno"] == DBNull.Value ? "" : dr["barno"].ToString(),
                    IsSMed = dr["barno"] == DBNull.Value ? false : true
                });
            }
            return returnlist;
        }
    }
}
